package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;


public class ScoreDTO {
	
	private ScoreVo vo;
	private Connection conn;
	
	//페이지와 관련된 변수
	int listSize=5;  //페이지당 표시 되는 행수
	int blockSize=5; //한블럭당 표시되는 페이지 수
	int totSize=0;   //전체 게시물의 갯수
	int totPage=0;   //전체 페이지의 갯수
	int totBlock=0;  //전체 블럭수
	int nowPage=1;   //현재 페이지
	int nowBlock=1;  //현재 블럭
	int startNo=0;   //리스트 목록의 시작 위치
	int endNo=0;     //리스트 목록의 끝 위치
	int startPage=0; //현재블럭에서 표시되는 시작 블럭번호
	int endPage=0;   //현재블럭에서 표시되는 끝 블럭 번호
 	
	
	public ScoreDTO() {
		conn=new MyOracle().getConn();
	}
	
	public void page1(){//페이지 관련 연산
		String sql="select count(*)cnt from score where mid like ? or exam ?";
		try {
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setString(2, vo.getFind());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				totSize=rs.getInt("cnt");
				//전체 페이지수
				totPage=(int)Math.ceil(totSize*1.0/listSize);
				//전체 블럭수
				totBlock=(int)Math.ceil(totPage*1.0/ blockSize);
				//현재 블럭
				nowBlock =(int)Math.ceil(nowPage*1.0/blockSize);
				//페이지의 시작과 끝번호
				endPage=nowBlock*blockSize;
				startPage=endPage-blockSize+1;
				//게시물의 시작번화와 끝 번호
				endNo=nowPage*listSize;
				startNo=endNo-listSize+1;
				
				//endPage, endNo 보정값
				if(endPage>totPage)endPage=totPage;
				if(endNo>totSize)endNo=totSize;
			}
			System.out.println("<li>totSize="+totSize);
			System.out.println("<li>totPage="+totPage);
			System.out.println(nowPage);
			System.out.println(nowBlock);
			System.out.println();
			System.out.println();
			System.out.println();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	public void page(){ //페이지관련 연산
		String sql = "select count(*)cnt from score where mid like ? or exam like ?";
		
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1,"%"+ vo.getFind()+"%");
			pst.setString(2,"%"+ vo.getFind()+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				totSize = rs.getInt("cnt");
				//전체 페이지수
				totPage = (int)Math.ceil(totSize*1.0/listSize);
				//전체 블럭수
				totBlock=(int)Math.ceil(totPage*1.0/blockSize);
				//현재 블럭
				nowBlock = (int)Math.ceil(nowPage*1.0/blockSize);
				//페이지의 시작과 끝 번호				
				endPage = nowBlock * blockSize;
				startPage = endPage-blockSize+1;
				//게시물의 시작번호 끝번호
				endNo = nowPage*listSize;
				startNo = endNo-listSize+1;
				//현재 블럭
				// endPage, endNo 보정값
				if(endPage>totPage) endPage=totPage;
				if(endNo > totSize) endNo=totSize;
				
			}
			System.out.println("totSize="+totSize);
			System.out.println("<li>totPage="+totPage);
			System.out.println("<li>totBlock="+totBlock);
			System.out.println(endPage);
			System.out.println(startNo);
			System.out.println(startPage);
			System.out.println(endNo);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}	
	}

	public ArrayList<ScoreVo> select(){
		page();
		ArrayList<ScoreVo> al=new ArrayList<ScoreVo>();
		String sql = 
	            "select * from("
	            +"   select rownum rn, c.* from( "
	            +"   select * from score where mid like ? or exam like ?)c"
	            +"   )b "
	            +"where b.rn between ? and ?";
		try {
		PreparedStatement pst =conn.prepareStatement(sql);
		pst.setString(1, "%"+vo.getFind()+"%");
		pst.setString(2, "%"+vo.getFind()+"%");
		pst.setInt(3, startNo);
		pst.setInt(4, endNo);
		
		ResultSet rs= pst.executeQuery();
		while(rs.next()){
			ScoreVo v=new ScoreVo();
			v.setMid(rs.getString("mid"));
			v.setGrade(rs.getString("grade"));
			v.setPoint(rs.getInt("point"));
			v.setRno(rs.getInt("rno"));
			v.setExam(rs.getString("exam"));
			al.add(v);
		}
		rs.close();
		pst.close();
		conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return al;
	
	}
	
	public ScoreVo SelectOne(){
		ScoreVo v= new ScoreVo();
		String sql="select * from score where rno=?";
		try {
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				v.setRno(rs.getInt("rno"));
				v.setExam(rs.getString("exam"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}

	
	public boolean insert()throws Exception{
		boolean flag=false;
		int s=99;
		String sql="insert into score(mid,grade,photo,rno) values(?,?,?,SEQ_SCORE_RNO.nextval)";
		PreparedStatement pst=conn.prepareStatement(sql);
		pst.setString(1, vo.getMid());
		pst.setString(2, vo.getGrade());
		pst.setString(3, vo.getPhoto());
		int cnt=pst.executeUpdate();
		if(cnt>0)flag=true;
		
		pst.close();
		return flag;
		
	}
	
	public boolean delete(){
		boolean b=false;
		String sql="delete from score where rno=?";
		try {
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			int cnt=pst.executeUpdate();
			if(cnt>0) b=true;
			
			pst.close();
			conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return b;
	}
	
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public ScoreVo getVo() {
		return vo;
	}

	public void setVo(ScoreVo vo) {
		this.vo = vo;
		this.nowPage=vo.getNowPage();
		this.nowBlock=vo.getNowBlock();
		int size=1024*1024*10;
		String saveDir="C:/eclipse/JavaWorkspace/1406_web/WebContent/memberPhoto/";
		String oriFile="";
		String sysFile="";
		MultipartRequest multi=null;
		try {
			multi=new MultipartRequest(vo.getRequst(), saveDir, size, "utf-8", new DefaultFileRenamePolicy());
			Enumeration files =multi.getFileNames();
			String tempFile=(String)files.nextElement();
			oriFile=multi.getOriginalFileName(tempFile);
			sysFile=multi.getFilesystemName(tempFile);
						
			vo.setMid(multi.getParameter("mid"));
			vo.setExam(multi.getParameter("exam"));
			vo.setPhoto(sysFile);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	
}
